﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sicurezza.CA.Entita;
using System.Data.OleDb;
using System.Configuration;
using System.Web;
using System.IO;
using Sicurezza.Entita;
using OpenSSL.X509;

namespace Sicurezza.CA.DataProvider
{
    public class RequestDataProvider
    {

        public List<Request> GetAll()
        {
            List<Request> list;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT * " +
                               "FROM Requests r INNER JOIN Profiles p ON r.ProfileId = p.Id ";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();;

                list = BuildObjects(cmd.ExecuteReader());
            }

            return list;
        }

        public List<Request> GetWaiting()
        {
            List<Request> list;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT * " +
                               "FROM Requests r INNER JOIN Profiles p ON r.ProfileId = p.Id "+
                               "WHERE State=" + (int)RequestState.Waiting;

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open(); ;

                list = BuildObjects(cmd.ExecuteReader());
            }

            return list;
        }

        private List<Request> BuildObjects(OleDbDataReader dr)
        {
            List<Request> reqs = new List<Request>();
            Request r = null;
            while (dr.Read())
            {
                r = new Request();
                r.Id = Convert.ToInt32(dr["r.Id"]);
                r.Subject = "C=" + Convert.ToString(dr["C"]);
                r.Subject += "; O=" + Convert.ToString(dr["O"]);
                r.Subject += "; OU=" + Convert.ToString(dr["OU"]);
                r.Subject += "; CN=" + Convert.ToString(dr["CN"]);
                r.Subject += "; Email= " + Convert.ToString(dr["Email"]);
                r.Timestamp = Convert.ToDateTime(dr["Timestamp"]);
                r.digitalSignature = Convert.ToBoolean(dr["DigitalSignature"]);
                r.keyEncipherment = Convert.ToBoolean(dr["KeyEncipherment"]);

                reqs.Add(r);


            }

            return reqs;
        }

        public void Delete(string ReqId)
        {
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "DELETE FROM Requests " +
                               "WHERE Id="+ReqId;

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open(); ;

                cmd.ExecuteNonQuery();
            }
        }

        public string Check(int ReqId)
        {
            string reason = null;

            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "SELECT * FROM Requests " +
                               "WHERE Id=" + ReqId.ToString();

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();

                OleDbDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                {

                    RequestState rs = (RequestState)Convert.ToByte(dr["State"]);
                    if (rs == RequestState.Refused)
                        reason = Convert.ToString(dr["Reason"]);
                }
            }

            return reason;
        }

        public int Save(X509Request r,int ProfileId, RequestState rs, string reason)
        {
            int serial = 0;
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "INSERT INTO Requests " +
                               "([Timestamp],DigitalSignature,KeyEncipherment,ProfileId,State,Reason) " +
                               "VALUES (" + getInsertValues(r,ProfileId,rs,reason) + ")";

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();

                cmd.CommandText = "SELECT @@Identity";
                serial = Convert.ToInt32(cmd.ExecuteScalar());
            }

            return serial;
        }


        private string getInsertValues(X509Request r, int ProfileId, RequestState rs, string reason)
        {
            // YYYY-MM-DD HH:MM:DD
            StringBuilder sb = new StringBuilder();

            bool digitalSignature = false;
                bool keyEncipherment = false;

            string keyUsage = r.Subject.GetTextByName("keyUsage");
            if (keyUsage != null)
            {
                digitalSignature = keyUsage.Contains("digitalSignature");
                keyEncipherment = keyUsage.Contains("keyEncipherment");
            }

            sb.Append("#" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "#");

            sb.Append(",");
            sb.Append(digitalSignature.ToString());
            sb.Append(",");
            sb.Append(keyEncipherment.ToString());
            sb.Append(",");
            sb.Append(ProfileId);
            sb.Append(",");
            sb.Append((int)rs);
            sb.Append(",");
            sb.Append("'" + reason +"'");


            return sb.ToString();
        }



        public void SetState(int ReqId, RequestState requestState)
        {
            SetState(ReqId, requestState, "");
        }

        public void SetState(int ReqId, RequestState requestState, string reason)
        {
            using (OleDbConnection conn = new OleDbConnection(ConnectionStringDataProvider.ConnectionString))
            {
                string query = "UPDATE Requests " +
                               "SET State=" + (int)requestState + ",Reason='" + reason +"' " + 
                               "WHERE Id=" + ReqId;

                OleDbCommand cmd = new OleDbCommand(query, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }
    }
}
